package com.ifast.common.utils;

import java.awt.image.BufferedImage;
import java.io.BufferedInputStream;
import java.io.BufferedReader;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.Reader;
import java.io.StringWriter;
import java.net.URLEncoder;
import java.sql.Clob;
import java.time.LocalDateTime;
import java.time.ZoneOffset;
import java.time.format.DateTimeFormatter;
import java.util.List;
import java.util.Map;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

import javax.imageio.ImageIO;
import javax.servlet.http.Cookie;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;

import org.apache.poi.hssf.converter.ExcelToHtmlConverter;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xwpf.converter.core.BasicURIResolver;
import org.apache.poi.xwpf.converter.core.FileImageExtractor;
import org.apache.poi.xwpf.converter.xhtml.XHTMLConverter;
import org.apache.poi.xwpf.converter.xhtml.XHTMLOptions;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.xiaoleilu.hutool.util.CollectionUtil;
import com.xiaoleilu.hutool.util.StrUtil;

import cn.afterturn.easypoi.word.WordExportUtil;
import cn.afterturn.easypoi.word.entity.WordImageEntity;
import net.sf.jxls.transformer.XLSTransformer;

/**
 * Description 导出工具类
 * Author chenmingming
 * CreateTime 2018-05-07 20:54
 **/

@SuppressWarnings("deprecation")
public class ExportUtil {
    private static Logger logger = LoggerFactory.getLogger(ExportUtil.class);

    private static final String IMG_PATH = "";

    private static final String IMG_JPG_SUFFIX = "jpg";


    /**
     * 纯导出模板
     *
     * @param filePath    文件路径
     * @param newFileName 新文件名
     * @param response    httpresponse
     * @param isWord      是否是word/excel
     */
    public static void exportTemplet(String filePath, String newFileName, HttpServletResponse response, boolean isWord) {
        
        try(InputStream in = new FileInputStream(new File(filePath));
        		OutputStream out = response.getOutputStream()){
            response.reset();
            response.setCharacterEncoding("utf-8");
            if (isWord) {
                response.setContentType("application/msword");
                response.setHeader("Content-Disposition", "attachment;filename=" + new String((newFileName + ".docx").getBytes(), "iso-8859-1"));
            } else {
                response.setContentType("application/vnd.ms-excel;charset=utf-8");
                response.setHeader("Content-Disposition", "attachment;filename=" + new String((newFileName + ".xls").getBytes(), "iso-8859-1"));
            }
            byte[] b = new byte[1024];
            int length;
            while ((length = in.read(b)) > 0) {
                out.write(b, 0, length);
            }
        } catch (Exception e) {
            logger.error(e.toString());
        } 
    }

    /**
     * word转化HTML
     * @param docPath  word路径
     * @param htmlPath HTML路径
     */
    public static void WordToHtml(String docPath, String htmlPath){
          
        try(OutputStream out = new FileOutputStream(htmlPath);
        	InputStream in = new FileInputStream(docPath)){ 
            //加载html页面时图片路径  图片保存文件夹路径
            XHTMLOptions.create().URIResolver(new BasicURIResolver("./")).setExtractor(new FileImageExtractor(new File(IMG_PATH)));
            XHTMLConverter.getInstance().convert(new XWPFDocument(in), out, null);
        } catch (IOException e) {
            logger.error(e.toString());
        }
    }

   /**
    * excel转化HTML
    * @param excelPath
    * @param htmlPath
    */
    public static void excelToHtml(String excelPath, String htmlPath) {
    	
        try(InputStream in = new FileInputStream(new File(excelPath));
        	OutputStream out = new FileOutputStream(new File(htmlPath));
        	StringWriter writer  = new StringWriter();){
             
            HSSFWorkbook workBook = new HSSFWorkbook(in);
            ExcelToHtmlConverter converter = new ExcelToHtmlConverter(DocumentBuilderFactory.newInstance().newDocumentBuilder().newDocument());
            converter.setOutputColumnHeaders(false);
            converter.setOutputRowNumbers(false);
            converter.processWorkbook(workBook); 
            
            Transformer serializer = TransformerFactory.newInstance().newTransformer();
            serializer.setOutputProperty(OutputKeys.ENCODING, "UTF-8");
            serializer.setOutputProperty(OutputKeys.INDENT, "yes");
            serializer.setOutputProperty(OutputKeys.METHOD, "html");
            serializer.transform(new DOMSource(converter.getDocument()),new StreamResult(writer));
           
            out.write(writer.toString().getBytes("UTF-8"));
        } catch (Exception e) {
            logger.error(e.toString());
        }
    }

    /**
     * excel导出
     *
     * @param filePath    模板路径
     * @param newFileName 文件下载名称
     * @param response    httpresponse
     * @param excelInfo   excel数据
     * @param merge       合并信息
     */
    public static void exportExcel(String filePath, String newFileName, HttpServletResponse response, Map<String, Object> excelInfo, List<CellRangeAddress> merge, HttpServletRequest request) {
       
        try(OutputStream out = response.getOutputStream();
        		InputStream in = new FileInputStream(filePath);){
            
            Boolean flag= request.getHeader("User-Agent").indexOf("like Gecko")>0;
            if (request.getHeader("User-Agent").toUpperCase().indexOf("MSIE") != -1 || flag) {
                newFileName = URLEncoder.encode(newFileName, "UTF-8");
            } else {
                newFileName = new String(newFileName.getBytes("UTF-8"), "ISO8859-1");
            }
            
            response.reset();
            Cookie cookie = new Cookie("downloadState", "success");
            response.addCookie(cookie);
            response.setCharacterEncoding("utf-8");
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + newFileName);

            XLSTransformer transformer = new XLSTransformer();
            XSSFWorkbook workBook = (XSSFWorkbook) transformer.transformXLS(in, excelInfo);
           /* XSSFSheet xssfSheet = workBook.getSheetAt(0);
            xssfSheet.autoSizeColumn(0);
            xssfSheet.autoSizeColumn(14);*/
            XSSFCellStyle cellStyle = workBook.createCellStyle();
            cellStyle.setWrapText(true);
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            if (merge != null && !merge.isEmpty()) {
                for (CellRangeAddress temp : merge) {
                    workBook.getSheetAt(0).addMergedRegion(temp);
                }
            }
            resetCellFormula(workBook);
            workBook.write(out);
            out.flush();
        } catch (Exception e) {
            logger.error(e.toString());
        }
    }
    /**
     * excel导出
     *
     * @param filePath    模板路径
     * @param newFileName 文件下载名称
     * @param response    httpresponse
     * @param excelInfo   excel数据
     * @param mergeAll       合并信息
     */
    public static void exportExcelForZzmc(String filePath, String newFileName, HttpServletResponse response, Map<String, Object> excelInfo, List<List<CellRangeAddress>> mergeAll, HttpServletRequest request) {
        
    	try(OutputStream out = response.getOutputStream();
        		InputStream in = new FileInputStream(filePath);){
           
            Boolean flag= request.getHeader("User-Agent").indexOf("like Gecko")>0;
            if (request.getHeader("User-Agent").toUpperCase().indexOf("MSIE") != -1 || flag) {
                newFileName = URLEncoder.encode(newFileName, "UTF-8");
            } else {
                newFileName = new String(newFileName.getBytes("UTF-8"), "ISO8859-1");
            }
            response.reset();
            Cookie cookie = new Cookie("downloadState", "success");
            response.addCookie(cookie);
            response.setCharacterEncoding("utf-8");
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + newFileName);

            XLSTransformer transformer = new XLSTransformer();
            XSSFWorkbook workBook = (XSSFWorkbook) transformer.transformXLS(in, excelInfo);
           /* XSSFSheet xssfSheet = workBook.getSheetAt(0);
            xssfSheet.autoSizeColumn(0);
            xssfSheet.autoSizeColumn(14);*/
            XSSFCellStyle cellStyle = workBook.createCellStyle();
            cellStyle.setWrapText(true);
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            for (int i=0;i<mergeAll.size();i++) {
                List<CellRangeAddress> merge = mergeAll.get(i);
                if (merge != null && !merge.isEmpty()) {
                    for (CellRangeAddress temp : merge) {
                        workBook.getSheetAt(i).addMergedRegion(temp);
                    }
                }
            }
            resetCellFormula(workBook);
            workBook.write(out);
            out.flush();
        } catch (Exception e) {
            logger.error(e.toString());
        }
    }



    public static void exportExcelBd(String descPath,String finalPath,String filePath,Map<String, Object> excelInfo, List<CellRangeAddress> merge) {
     
        try(InputStream in = new FileInputStream(filePath);
        	FileOutputStream  fos = new FileOutputStream(finalPath);){
        	
            XLSTransformer transformer = new XLSTransformer();
            XSSFWorkbook workBook = (XSSFWorkbook) transformer.transformXLS(in, excelInfo);
            XSSFCellStyle cellStyle = workBook.createCellStyle();
            cellStyle.setWrapText(true);
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            if (merge != null && !merge.isEmpty()) {
                for (CellRangeAddress temp : merge) {
                    workBook.getSheetAt(0).addMergedRegion(temp);
                }
            }
            resetCellFormula(workBook);
            File savefile = new File(descPath);
            if (!savefile.exists()) {
                savefile.mkdirs();
            } 
            workBook.write(fos);
             
        } catch (Exception e) {
            logger.error(e.toString(),e);
        }
    }

    public static void exportExcelSetSheetName(String filePath,String newFilePath,String newFileName, Map<String, Object> excelInfo){
        XSSFWorkbook wb ; 
        try ( FileOutputStream fos = new FileOutputStream("F:/excel/" + newFilePath + "/" + newFileName);
        	  InputStream in = new FileInputStream(filePath);){
           
            String sheetName = excelInfo.get("sheet").toString();
            XLSTransformer transformer = new XLSTransformer();
            wb = (XSSFWorkbook) transformer.transformXLS(in, excelInfo);
            wb.setSheetName(wb.getActiveSheetIndex(),sheetName);
            File savefile = new File("F:/excel/" + newFilePath + "/");
            if (!savefile.exists()) {
                savefile.mkdirs();
            } 
            wb.write(fos);
            
        }catch (Exception e){
            e.printStackTrace();
            wb = new XSSFWorkbook();
        }
    }

    /**
     * excel导出文件
     *
     * @param filePath    模板路径
     * @param newFileName 文件下载名称
     * @param excelInfo   excel数据
     * @param merge       合并信息
     */
    public static void exportExcelForBendi(String id, String filePath, String newFileName, Map<String, Object> excelInfo, List<CellRangeAddress> merge) {
        
        
        try(InputStream in = new FileInputStream(filePath);
        	FileOutputStream fos = new FileOutputStream("F:/excel/" + id + "/" + newFileName);){ 
            
            XSSFWorkbook workBook = (XSSFWorkbook) new XLSTransformer().transformXLS(in, excelInfo);

            if (merge != null && !merge.isEmpty()) {
                for (CellRangeAddress temp : merge) {
                    workBook.getSheetAt(0).addMergedRegion(temp);
                }
            }
            File savefile = new File("F:/excel/" + id + "/");
            if (!savefile.exists()) {
                savefile.mkdirs();
            }  
            workBook.write(fos);
          
        } catch (Exception e) {
            logger.error(e.toString());
        }
    }

    /**
     * excel导出含图片的excel(针对比较简单的excel模板)
     *
     * @param filePath    模板路径
     * @param newFileName 文件下载名称
     * @param response    httpresponse
     * @param excelInfo   excel数据
     * @param merge       合并信息
     * @param begColIndex 起始列
     * @param colPlus     列增长
     * @param begRowIndex 起始列
     * @param rowPlus     行增长
     */

	@SuppressWarnings("unchecked")
	public static void exportPicExcel(String filePath, String newFileName, HttpServletResponse response, Map<String, Object> excelInfo, List<CellRangeAddress> merge,
                                      int begColIndex, int colPlus, int begRowIndex, int rowPlus, HttpServletRequest request) {
        List<Map<String, Object>> list = null;
        //String imgRootPath = Const.filePath.split(":")[0] + ":";
        //画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
        XSSFDrawing patriarch = null;
        XSSFClientAnchor anchor = null;
        BufferedImage bufferImg = null;
        ByteArrayOutputStream byteArrayOut = null;

        try ( OutputStream out = response.getOutputStream();
        		InputStream in = new FileInputStream(filePath);){
            
            list = (List<Map<String, Object>>) excelInfo.get("list");

            response.setCharacterEncoding("utf-8");
            response.setContentType("application/vnd.ms-excel");
            Boolean flag= request.getHeader("User-Agent").indexOf("like Gecko")>0;
            if (request.getHeader("User-Agent").toUpperCase().indexOf("MSIE") != -1 || flag) {
                newFileName = URLEncoder.encode(newFileName, "UTF-8");
            } else {
                newFileName = new String(newFileName.getBytes("UTF-8"), "ISO8859-1");
            }
            response.setHeader("Content-Disposition", "attachment;filename=" +newFileName);

            XLSTransformer transformer = new XLSTransformer();
            XSSFWorkbook workBook = (XSSFWorkbook) transformer.transformXLS(in, excelInfo);
            XSSFSheet sheet = workBook.getSheetAt(0);

            if (CollectionUtil.isNotEmpty(list)) {
                for (int i = 0; i < list.size(); i++) {
                    if (list.get(i).get("photo") != null) {
                        try {
                            byteArrayOut = new ByteArrayOutputStream();
                            bufferImg = ImageIO.read(new File((list.get(i).get("photo").toString()).replaceAll("\\\\", "/")));
                            ImageIO.write(bufferImg, IMG_JPG_SUFFIX, byteArrayOut);

                            //画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
                            patriarch = sheet.createDrawingPatriarch();
                            //anchor主要用于设置图片的属性
                            anchor = new XSSFClientAnchor(0, 0, 0, 0, begColIndex, i + begRowIndex, (begColIndex + colPlus), (i + begRowIndex + rowPlus));
                            anchor.setAnchorType(3);
                            //插入图片
                            patriarch.createPicture(anchor, workBook.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
                        } catch (Exception e) {
                            logger.error(e.toString());
                        }
                    }
                }
            }

            if (merge != null && !merge.isEmpty()) {
                for (CellRangeAddress temp : merge) {
                    workBook.getSheetAt(0).addMergedRegion(temp);
                }
            }

            workBook.write(out);
            out.flush();
        } catch (Exception e) {
            logger.error(e.toString());
        }
    }

    /**
     * excel导出文件
     *
     * @param filePath    模板路径
     * @param newFileName 文件下载名称
     * @param excelInfo   excel数据
     * @param merge       合并信息
     */

	@SuppressWarnings("unchecked")
	public static void exportExcelForBendiPic(String descPath,String finalPath,String filePath, String newFileName, Map<String, Object> excelInfo, List<CellRangeAddress> merge,
                                              int begColIndex, int colPlus, int begRowIndex, int rowPlus) {
        
        List<Map<String, Object>> list;
        //画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
        XSSFDrawing patriarch;
        XSSFClientAnchor anchor;
        BufferedImage bufferImg;
        ByteArrayOutputStream byteArrayOut;
        try(InputStream in = new FileInputStream(filePath);
        		FileOutputStream fos = new FileOutputStream(finalPath);){
           
            XLSTransformer transformer = new XLSTransformer();
            XSSFWorkbook workBook = (XSSFWorkbook) transformer.transformXLS(in, excelInfo);
            XSSFSheet sheet = workBook.getSheetAt(0);
            
            list = (List<Map<String, Object>>) excelInfo.get("list");

            if (CollectionUtil.isNotEmpty(list)) {
                for (int i = 0; i < list.size(); i++) {
                    if (list.get(i).get("photo") != null) {
                        try {
                            byteArrayOut = new ByteArrayOutputStream();
                            bufferImg = ImageIO.read(new File((list.get(i).get("photo").toString()).replaceAll("\\\\", "/")));
                            ImageIO.write(bufferImg, IMG_JPG_SUFFIX, byteArrayOut);

                            //画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
                            patriarch = sheet.createDrawingPatriarch();
                            //anchor主要用于设置图片的属性
                            anchor = new XSSFClientAnchor(0, 0, 0, 0, begColIndex, i + begRowIndex, (begColIndex + colPlus), (i + begRowIndex + rowPlus));
                            anchor.setAnchorType(3);
                            //插入图片
                            patriarch.createPicture(anchor, workBook.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
                        } catch (Exception e) {
                            logger.error(e.toString(),e);
                        }
                    }
                }
            }

            if (merge != null && !merge.isEmpty()) {
                for (CellRangeAddress temp : merge) {
                    workBook.getSheetAt(0).addMergedRegion(temp);
                }
            }
            if (merge != null && !merge.isEmpty()) {
                for (CellRangeAddress temp : merge) {
                    workBook.getSheetAt(0).addMergedRegion(temp);
                }
            }
            File savefile = new File(descPath);
            if (!savefile.exists()) {
                savefile.mkdirs();
            }
            workBook.write(fos);
             
        } catch (Exception e) {
            logger.error(e.toString(),e);
        } 
    }


    /**
     * excel导出含图片的excel(针对比较简单的excel模板)
     *
     * @param filePath    模板路径
     * @param newFileName 文件下载名称
     * @param response    httpresponse
     * @param excelInfo   excel数据
     * @param merge       合并信息
     * @param begColIndex 起始列
     * @param colPlus     列增长
     * @param begRowIndex 起始列
     * @param rowPlus     行增长
     */
    @SuppressWarnings("unchecked")
	public static void exportPicExcelKhcp(String filePath, String newFileName, HttpServletResponse response, Map<String, Object> excelInfo, List<CellRangeAddress> merge,
                                          int begColIndex, int colPlus, int begRowIndex, int rowPlus, HttpServletRequest request) {
       
        List<Map<String, Object>> list = null;
       // String imgRootPath = Const.filePath.split(":")[0] + ":";
        //画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
        XSSFDrawing patriarch = null;
        XSSFClientAnchor anchor = null;
        BufferedImage bufferImg = null;
        ByteArrayOutputStream byteArrayOut = null;

        try ( OutputStream  out = new FileOutputStream(newFileName);
                InputStream  in = new FileInputStream(filePath);){
           
            
            list = (List<Map<String, Object>>) excelInfo.get("list");

//            response.setCharacterEncoding("utf-8");
//            response.setContentType("application/vnd.ms-excel");
//            Boolean flag= request.getHeader("User-Agent").indexOf("like Gecko")>0;
//            if (request.getHeader("User-Agent").toUpperCase().indexOf("MSIE") != -1 || flag) {
//                newFileName = URLEncoder.encode(newFileName, "UTF-8");
//            } else {
//                newFileName = new String(newFileName.getBytes("UTF-8"), "ISO8859-1");
//            }
//            response.setHeader("Content-Disposition", "attachment;filename=" +newFileName);

            XLSTransformer transformer = new XLSTransformer();
            XSSFWorkbook workBook = (XSSFWorkbook) transformer.transformXLS(in, excelInfo);
            XSSFSheet sheet = workBook.getSheetAt(0);

            if (CollectionUtil.isNotEmpty(list)) {
                for (int i = 0; i < list.size(); i++) {
                    if (list.get(i).get("photo") != null) {
                        try {
                            byteArrayOut = new ByteArrayOutputStream();
                            bufferImg = ImageIO.read(new File((list.get(i).get("photo").toString()).replaceAll("\\\\", "/")));
                            ImageIO.write(bufferImg, IMG_JPG_SUFFIX, byteArrayOut);

                            //画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
                            patriarch = sheet.createDrawingPatriarch();
                            //anchor主要用于设置图片的属性
                            anchor = new XSSFClientAnchor(20000, 20000, -20000, -20000, begColIndex, (i*4) + begRowIndex, (begColIndex + colPlus), ((i*4) + begRowIndex + rowPlus));
                            anchor.setAnchorType(3);
                            //插入图片
                            patriarch.createPicture(anchor, workBook.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
                        } catch (Exception e) {
                            logger.error(e.toString());
                        }
                    }
                }
            }

            if (merge != null && !merge.isEmpty()) {
                for (CellRangeAddress temp : merge) {
                    workBook.getSheetAt(0).addMergedRegion(temp);
                }
            }

            workBook.write(out);
            out.flush();
        } catch (Exception e) {
            logger.error(e.toString());
        }
    }

    /**
     * word模板导出
     *
     * @param filePath    模板路径
     * @param newFileName 文件下载名
     * @param picPath     图片路径
     * @param response    httpresponse
     * @param map         模板参数
     */
    
	public static void exportWord(String filePath, String newFileName, String picPath, HttpServletResponse response, Map<String, Object> map) {
       
        ByteArrayOutputStream ostream = null;
       // String imgRootPath = Const.filePath.split(":")[0] + ":";
        try ( InputStream in = new FileInputStream(new File(filePath));
                OutputStream out = response.getOutputStream();){ 
            
            ostream = new ByteArrayOutputStream();

            if (!StrUtil.isNotBlank(picPath)) {
                WordImageEntity image = new WordImageEntity();
                image.setHeight(225);
                image.setWidth(168);
               // image.setUrl((imgRootPath + picPath).replaceAll("\\\\", "/"));
                image.setType(WordImageEntity.URL);
                map.put("PHOTO", image);
            }
            XWPFDocument wordFile = WordExportUtil.exportWord07(filePath, map);

            //输出word内容文件流，提供下载
            response.setContentType("application/x-msdownload");
            response.setHeader("Content-Disposition", "attachment;filename=" + new String((newFileName).getBytes(), "iso-8859-1"));

            wordFile.write(ostream);
            out.write(ostream.toByteArray());
            out.flush();
        } catch (Exception e) {
            logger.error(e.toString());
        } 
    }

    /**
     * clob 转 string
     *
     * @param clob
     * @return
     */
    public static String clob2String(Clob clob) {
        String reString = "";
        try {
            if (clob != null) {
                Reader is = clob.getCharacterStream();// 得到流
                BufferedReader br = new BufferedReader(is);
                String s = br.readLine();
                StringBuffer sb = new StringBuffer();
                while (s != null) {// 执行循环将字符串全部取出付值给StringBuffer由StringBuffer转成STRING
                    sb.append(s);
                    s = br.readLine();
                }
                reString = sb.toString();
            }
        } catch (Exception e) {
            logger.error(e.toString());
        }

        return reString;
    } 

    /**
     * 日期转化
     *
     * @param time
     * @return
     */
    public static String timeToString(String time) {
        String result = null;
        try {
            String[] times = time.split("-");
            result = times[0] + "年" + times[1] + "月" + times[2] + "日";
        } catch (Exception e) {
            logger.error(e.toString());
        }
        return result;
    }

    /**
     * 重新设置单元格计算公式
     */
    public static void resetCellFormula(XSSFWorkbook wb) {
        XSSFFormulaEvaluator e = new XSSFFormulaEvaluator(wb);
        int sheetNum = wb.getNumberOfSheets();
        for (int i = 0; i < sheetNum; i++) {
            XSSFSheet sheet = wb.getSheetAt(i);
            int rows = sheet.getLastRowNum() + 1;
            for (int j = 0; j < rows; j++) {
                XSSFRow row = sheet.getRow(j);
                if (row == null){
                    continue;
                }
                int cols = row.getLastCellNum();
                for (int k = 0; k < cols; k++) {
                    XSSFCell cell = row.getCell(k);
                    if (cell != null){
                        GjpLogger.info("cell[" + j + "," + k + "]=:" + cell.getCellType());
                    }
                    if (cell == null){
                        continue;
                    }
                    if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
                        cell.setCellFormula(cell.getCellFormula());
                        GjpLogger.info("----公式：" + cell.getCellFormula());
                        cell = e.evaluateInCell(cell);
                        GjpLogger.info("-----------" + cell.getNumericCellValue());
                    }
                }
            }
        }
    }

    /**
     * excel导出(日志统计用)
     *
     * @param filePath    模板路径
     * @param newFileName 文件下载名称
     * @param response    httpresponse
     * @param excelInfo   excel数据
     * @param merge       合并信息
     */
    public static void exportExcelRztj(String filePath, String newFileName, HttpServletResponse response, Map<String, Object> excelInfo, List<CellRangeAddress> merge, HttpServletRequest request) {
        
        try (OutputStream out = response.getOutputStream();
                InputStream in = new FileInputStream(filePath);){ 
        	
            Boolean flag= request.getHeader("User-Agent").indexOf("like Gecko")>0;
            if (request.getHeader("User-Agent").toUpperCase().indexOf("MSIE") != -1 || flag) {
                newFileName = URLEncoder.encode(newFileName, "UTF-8");
            } else {
                newFileName = new String(newFileName.getBytes("UTF-8"), "ISO8859-1");
            }
            Cookie cookie = new Cookie("downloadState", "success");
            response.addCookie(cookie);
            response.setCharacterEncoding("utf-8");
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + newFileName);

            XLSTransformer transformer = new XLSTransformer();
            XSSFWorkbook workBook = (XSSFWorkbook) transformer.transformXLS(in, excelInfo);
           /* XSSFSheet xssfSheet = workBook.getSheetAt(0);
            xssfSheet.autoSizeColumn(0);
            xssfSheet.autoSizeColumn(14);*/
            XSSFCellStyle cellStyle = workBook.createCellStyle();
            cellStyle.setWrapText(true);
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            if (merge != null && !merge.isEmpty()) {
                for (CellRangeAddress temp : merge) {
                    workBook.getSheetAt(1).addMergedRegion(temp);
                }
            }
            resetCellFormula(workBook);
            workBook.write(out);
            out.flush();
        } catch (Exception e) {
            logger.error(e.toString());
        }
    }


    public static void exportExcelRztj(String filePath, String newFileName, Map<String, Object> excelInfo, List<CellRangeAddress> merge,int sheetIndex) {
        
        try (OutputStream out = new FileOutputStream(newFileName);
                InputStream in = new FileInputStream(filePath);){ 

            XLSTransformer transformer = new XLSTransformer();
            XSSFWorkbook workBook = (XSSFWorkbook) transformer.transformXLS(in, excelInfo);
           /* XSSFSheet xssfSheet = workBook.getSheetAt(0);
            xssfSheet.autoSizeColumn(0);
            xssfSheet.autoSizeColumn(14);*/
            XSSFCellStyle cellStyle = workBook.createCellStyle();
            cellStyle.setWrapText(true);
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            if (merge != null && !merge.isEmpty()) {
                for (CellRangeAddress temp : merge) {
                    workBook.getSheetAt(sheetIndex).addMergedRegion(temp);
                }
            }
            resetCellFormula(workBook);
            workBook.write(out);
            out.flush();
        } catch (Exception e) {
            e.printStackTrace();
            logger.error(e.toString());
        } 
    }

    public static void exportExcelMzcp(String filePath, String newFileName, Map<String, Object> excelInfo, Map<Integer ,List<CellRangeAddress> > merge) {
         
        
        try (OutputStream out =new FileOutputStream(newFileName);
        		InputStream in = new FileInputStream(filePath);){ 
           
            XLSTransformer transformer = new XLSTransformer();
            XSSFWorkbook workBook = (XSSFWorkbook) transformer.transformXLS(in, excelInfo);

//            xssfSheet.autoSizeColumn(0);
//            xssfSheet.autoSizeColumn(14);
            XSSFCellStyle cellStyle = workBook.createCellStyle();
            cellStyle.setWrapText(true);
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            if (merge != null && !merge.isEmpty()) {
                for (Integer sheetIndex : merge.keySet()) {
                    XSSFSheet xssfSheet = workBook.getSheetAt(sheetIndex);
                    xssfSheet.setColumnWidth(1, 8000);
                    if (merge.get(sheetIndex)!=null && !merge.get(sheetIndex).isEmpty()){
                        for (CellRangeAddress temp : merge.get(sheetIndex)) {
                            workBook.getSheetAt(sheetIndex).addMergedRegion(temp);
                        }
                    }
                }

            }
            resetCellFormula(workBook);
            workBook.write(out);
            out.flush();
        } catch (Exception e) {
            e.printStackTrace();
            logger.error(e.toString());
        }
    }

    public static void exportExcelMzcpUser(String filePath, String newFileName, Map<String, Object> excelInfo, Map<Integer ,List<CellRangeAddress> > merge) {
          
        try (OutputStream out = new FileOutputStream(newFileName);
        		InputStream in = new FileInputStream(filePath);	){ 

            XLSTransformer transformer = new XLSTransformer();
            XSSFWorkbook workBook = (XSSFWorkbook) transformer.transformXLS(in, excelInfo);
            XSSFSheet xssfSheet = workBook.getSheetAt(1);
            XSSFSheet xssfSheet2 = workBook.getSheetAt(2);
//            xssfSheet.autoSizeColumn(0);
//            xssfSheet.autoSizeColumn(14);
            xssfSheet.setColumnWidth(1, 8000);
            xssfSheet2.setColumnWidth(1, 8000);
            xssfSheet2.setColumnWidth(2, 3000);
            XSSFCellStyle cellStyle = workBook.createCellStyle();
            cellStyle.setWrapText(true);
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            if (merge != null && !merge.isEmpty()) {
                for (Integer sheetIndex : merge.keySet()) {
                    if (merge.get(sheetIndex)!=null && !merge.get(sheetIndex).isEmpty()){
                        for (CellRangeAddress temp : merge.get(sheetIndex)) {
                            workBook.getSheetAt(sheetIndex).addMergedRegion(temp);
                        }
                    }
                }

            }
            resetCellFormula(workBook);
            workBook.write(out);
            out.flush();
        } catch (Exception e) {
            e.printStackTrace();
            logger.error(e.toString());
        }
    }

    public static void inputZipFile(HttpServletResponse response, String fileName, List<XSSFWorkbook> wbList){
    	
        try(ZipOutputStream zos = new ZipOutputStream(response.getOutputStream());){
            response.setContentType("application/x-msdownload");
            response.setHeader("Content-Disposition", "attachment;filename="
                    + new String(fileName.getBytes("GB2312"), "8859_1")
                    + ".zip");
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
            for(XSSFWorkbook wb :wbList){
                DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyyMMddHHmmssSSS");
                String dateTime = LocalDateTime.now(ZoneOffset.of("+8")).format(formatter);
                zos.putNextEntry(new ZipEntry(fileName+dateTime));
                ByteArrayOutputStream ostream = new ByteArrayOutputStream();
                wb.write(ostream);
                ByteArrayInputStream swapStream = new ByteArrayInputStream(ostream.toByteArray());
                BufferedInputStream  bis = new BufferedInputStream(swapStream, 1024 * 10);
                byte[] bufs = new byte[1024 * 10];
                int read = 0;
                while ((read = bis.read(bufs, 0, 1024 * 10)) != -1) {
                    zos.write(bufs, 0, read);
                }
                zos.flush();
            }
             
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}